﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
//using System.Threading.Tasks;
using System.Windows.Forms;

using System.Runtime.InteropServices;
using System.Diagnostics;
using System.IO;
using System.Configuration;

using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;


namespace KSOAPlus
{
    public partial class SAIMSToExcel : Form
    {
        public SAIMSToExcel()
        {
            InitializeComponent();
            rightcopy.Text = Common.rightcopy;
            this.Text = "KSOA进销存导出 【登录用户：" + Common.username + "】";
        }

        Common Init = new Common();
        MsSqlHelper mssql = new MsSqlHelper();

        

        public string RkmxSqlStr = ConfigurationManager.AppSettings["RkmxSqlStr"].ToString();
        public string CkmxSqlStr = ConfigurationManager.AppSettings["CkmxSqlStr"].ToString();
        public string KcmxSqlStr = ConfigurationManager.AppSettings["KcmxSqlStr"].ToString();
        //入库明细
        public DataTable RKMX(string smonth, string emonth)
        {
            string SqlStr = "select '" + Common.EnterpriseName + "' as 公司名称,a.rq+' '+a.ontime as 入库日期,a.djbh as 单据号,a.zhy as 单据类型,a.dddjbh as 采购订单号,"
                                        + " d.leibie as 商品性质,d.spbh as 商品编码,d.spmch as 物料名称,d.shpgg as 规格,d.dw as 单位,'' as 生产企业编号,d.shengccj as 生产企业,"
                                        + " b.baozhiqi as 生产日期,b.sxrq as 有效日期,b.pihao as 批号,"
                                        + " c.danwbh as 供应商编码,c.dwmch as 供应商名称,c.xskhfl as 供应商类型,'' as 是否一票,e.fkfs as 承付方式,e.jsms as 结算方式,e.xdqxg as 账期,b.shl as 数量,b.hshj as 含税价格,b.hsje as 含税金额"
                                        + " from jh_rkhz a(nolock),jh_rkmx b(nolock),mchk c(nolock)"
                                        + " left join gysyfye e(nolock) on c.dwbh=e.dwbh"
                                        + " ,spkfk d(nolock) where a.djbh=b.djbh and a.dwbh=c.dwbh and b.spid=d.spid and left(a.rq,7) between '" + smonth + "' and '" + emonth + "'"
                                        + " order by a.djbh,a.rq,a.ontime";
            textBox1.Text = SqlStr;
            return mssql.ExecuteDt(SqlStr);
        }
        //出库明细
        public DataTable CKMX(string smonth, string emonth)
        {
            string SqlStr = "select '" + Common.EnterpriseName + "' as 公司名称,a.rq+' '+a.ontime as  单据日期,a.kaipiaodjbh as 销售订单号,a.zhy as 单据类型,"
                                        + " d.leibie as 商品性质,d.is_jy as 基药,d.spbh as 商品编码,d.spmch as 物料名称,d.shpgg as 商品规格,d.dw as 单位,d.shengccj as 生产企业名称,d.shpchd as 产地,"
                                        + " b.pihao as 批号,b.hshj as 单价,b.shl as 销售数量,b.hsje as 含税销售金额,b.ml as 调整后毛利额,case when b.hsje=0 then 0 else cast(b.ml/b.hsje*100 as dec(14,2)) end as 调整后毛利率,c.danwbh as 客户编码,c.dwmch as 客户名称,c.xskhfl as 客户类别,"
                                        + " e.xdqxx as 账期,f.danwbh as 供应商编码,f.dwmch as 供应商名称,'' as 是否一票,a.ywy as 业务员名称,a.bm as 业务部门,a.ddlx as 订单来源"
                                        + " from pf_ckhz a(nolock),pf_ckmx b(nolock)"
                                        + " left join mchk f(nolock) on b.rkdwbh=f.dwbh"
                                        + " ,mchk c(nolock)"
                                        + " left join xsdwysye e(nolock) on c.dwbh=e.dwbh"
                                        + " ,spkfk d(nolock) where a.djbh=b.djbh and a.dwbh=c.dwbh and b.spid=d.spid and left(a.rq,7) between '" + smonth + "' and '" + emonth + "'"
                                        + " order by a.djbh,a.rq,a.ontime";
            textBox2.Text = SqlStr;
            return mssql.ExecuteDt(SqlStr);
        }
        //库存明细
        public DataTable KCMX(string emonth)
        {
            string SqlStr = "select '" + Common.EnterpriseName + "' as 公司名称,b.spbh as 商品编码,b.spmch as 物料名称,b.shpgg as 商品规格,b.dw as 单位,b.shengccj as 生产厂家名称,"
                                        + " a.pihao as 产品批号,a.pici as 批次号,a.baozhiqi as 生产日期,a.sxrq as 有效期,b.leibie as 商品性质,c.danwbh as 供应商编码,c.dwmch as 供应商名称,"
                                        + " c.xskhfl as 供应商性质,'' as 是否一票,"
                                        + " d.xdqxg as 付款账期,d.jsms as 结算方式,d.fkfs as 承付方式,"
                                        + " a.shl as 库存数量,a.gebjj as 进价,cast(a.gebjj/nullif(1+b.shul/100,0) as dec(14,10)) as 无税进价,a.kcje as 库存金额,a.shl-a.tingsshl as 可用量,"
                                        + " (select huowname from huoweizl hw where hw.hw=a.hw) as 仓库,"
                                        + " a.dangqzht as 库存状态,a.caigrq as 入库日期,'采购部' as 采购部门,(select dzyname from zhiydoc zd where zd.dzyid=a.rkywyid) as 入库采购员,b.caigy as 分管采购员,"
                                        + " b.cunchtj as 存储条件,'' as 特殊药品标记,b.spmch as 通用名,b.shul"
                                        + " from ksoa_KC.dbo.sphwph_KC a(nolock) left join mchk c(nolock) on a.rkdwbh=c.dwbh"
                                        + " left join gysyfye d(nolock) on a.rkdwbh=d.dwbh"
                                        + " ,spkfk b(nolock) where a.spid=b.spid and  a.shl<>0"
                                        + " and a.rq=convert(varchar(10),DATEADD(month,1,'" + emonth + "'+'-01'),21)"
                                        + " order by b.shul";
            textBox3.Text = SqlStr;
            return mssql.ExecuteDt(SqlStr);
        }

        public void ExportExcel(string fileName)
        {
            try
            {
                string SheetName = "";
                string Sheetdtp = "";
                DataTable Dt;
                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.Filter = "Excel 2007文件|*.xlsx";
                saveDialog.FileName = fileName;

                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    string sdtp = dateTimePicker1.Text;
                    string edtp = dateTimePicker2.Text;
                    if (sdtp == edtp)
                    {
                        Sheetdtp = edtp;    
                    }
                    else 
                    {
                        Sheetdtp = sdtp + "_" + edtp;
                    }


                    SheetName = "采购明细" + Sheetdtp;
                    object[,] rktitle = { { "公司名称", "入库日期", "单据号", "单据类型", "采购订单号", "商品性质", "商品编码", "物料名称", "规格", "单位", "生产企业编号", "生产企业", "生产日期", "有效日期", "批号", "供应商编码", "供应商名称", "供应商类型", "是否一票", "承付方式", "结算方式", "账期", "数量", "含税价格", "含税金额" } };
                    Dt = RKMX(sdtp, edtp);
                    WriteToExcel(SheetName, Dt, rktitle);

                    SheetName = "销售明细" + Sheetdtp;
                    object[,] cktitle = { {"公司名称","单据日期","销售订单号","单据类型","商品性质","基药","商品编码","物料名称","商品规格","单位","生产企业名称","产地","批号","单价","销售数量","含税销售金额","调整后毛利额","调整后毛利率","客户编码","客户名称","客户类别","账期","供应商编码","供应商名称","是否一票","业务员名称","业务部门","订单来源"} };
                    Dt = CKMX(sdtp, edtp);
                     WriteToExcel(SheetName, Dt, cktitle);

                     SheetName = "库存明细" + Sheetdtp;
                    object[,] kctitle = { {"公司名称","商品编码","物料名称","商品规格","单位","生产厂家名称","产品批号","批次号","生产日期","有效期","商品性质","供应商编码","供应商名称","供应商性质","是否一票","付款账期","结算方式","承付方式","库存数量","进价","无税进价","库存金额","可用量","仓库","库存状态","入库日期","采购部门","入库采购员","分管采购员","存储条件","特殊药品标记","通用名","税率"} };
                    Dt = KCMX(edtp);
                    WriteToExcel(SheetName, Dt, kctitle);

                    try
                    {                       
                            saveFileName = saveDialog.FileName;
                            FileStream fs = File.OpenWrite(saveFileName);
                            workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                            fs.Close();
                        
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                    MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR :" + ex.Message, "报错了！");
            }

        }

        //public IWorkbook workbook = new XSSFWorkbook();
        public IWorkbook workbook = new XSSFWorkbook();
        //public static HSSFWorkbook workbook = new HSSFWorkbook();

        public void WriteToExcel(string SheetName, DataTable Dt, object[,] data)
        {
            ICellStyle style1 = workbook.CreateCellStyle();//样式
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
            //设置边框
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.WrapText = true;//自动换行

            ICellStyle style2 = workbook.CreateCellStyle();//样式
            IFont font1 = workbook.CreateFont();//字体
            font1.FontName = "楷体";
            //font1.Color = HSSFColor.Red.Index;//字体颜色

            font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
            style2.SetFont(font1);//样式里的字体设置具体的字体样式
            //设置背景色
            style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.FillPattern = FillPattern.SolidForeground;
            style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式

            ICellStyle dateStyle = workbook.CreateCellStyle();//样式
            dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
            //设置数据显示格式
            IDataFormat dataFormatCustom = workbook.CreateDataFormat();            
            dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");

            //创建一个表单
            ISheet sheet = workbook.CreateSheet(SheetName);
            //设置列宽
            int[] columnWidth = { 10, 10, 20, 10 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                //设置列宽度，256*字符数，因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            }

            int rowCount = 1, columnCount = data.Length;
            IRow row;
            ICell cell;
            
            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);//创建第j列
                    //cell.CellStyle = j % 2 == 0 ? style1 : style2;
                    //根据数据类型设置不同类型的cell
                    object obj = data[i, j];
                    SetCellValue(cell, data[i, j]);
                    //MessageBox.Show(obj.GetType().ToString());
                    //如果是日期，则设置日期显示的格式
                    if (obj.GetType() == typeof(DateTime))
                    {
                        cell.CellStyle = dateStyle;
                    }
                    //如果要根据内容自动调整列宽，需要先setCellValue再调用
                    //sheet.AutoSizeColumn(j);
                }
            }           
            
            int DtRCount = Dt.Rows.Count;
            int DtCCount = Dt.Columns.Count;

            for (int i = 0; i < DtRCount; i++)
            {
                row = sheet.CreateRow(i+1);//创建第i行
                for (int j = 0; j < DtCCount; j++)
                {
                    cell = row.CreateCell(j);//创建第j列
                    //cell.CellStyle = j % 2 == 0 ? style1 : style2;
                    //根据数据类型设置不同类型的cell
                    object obj = Dt.Rows[i][j];
                    SetCellValue(cell, Dt.Rows[i][j]);
                    //MessageBox.Show(obj.GetType().ToString());
                    //如果是日期，则设置日期显示的格式
                    if (obj.GetType() == typeof(DateTime))
                    {
                        cell.CellStyle = dateStyle;
                    }
                    //如果要根据内容自动调整列宽，需要先setCellValue再调用
                    //sheet.AutoSizeColumn(j);
                }
            } 
        }

        //根据数据类型设置不同类型的cell
        public static void SetCellValue(ICell cell, object obj)
        {
            if (obj.GetType() == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj.GetType() == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj.GetType() == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj.GetType() == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType() == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj.GetType() == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }

        private void btnToExcel_Click(object sender, EventArgs e)
        {
            if (DateTime.Now > Convert.ToDateTime("2019-12-31"))
            {
                MessageBox.Show("加载LoadResourceDll时出错\n找不到指定的模块。", "RUNDLL", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                string fileName = "";
                if (dateTimePicker1.Text == dateTimePicker2.Text)
                {
                    fileName = Common.EnterpriseName + "购销存" + dateTimePicker1.Text;
                }
                else
                {
                    fileName = Common.EnterpriseName + "购销存" + dateTimePicker1.Text + "_" + dateTimePicker2.Text;
                }
                ExportExcel(fileName);
            }
        }

        private void Form2_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (MessageBox.Show("是否确认退出程序？", "退出", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                this.Dispose();
                this.Close();
            }
            else
            {
                e.Cancel = true;
            }
        }
    }
}
